package dr.item.dao;

import java.sql.*;
import java.util.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import dr.item.domain.Item;

public class ItemDAO {
	
	private static ItemDAO instance = new ItemDAO();
	
	public static ItemDAO getInstance(){
		return instance;
	}
	
	private ItemDAO(){}

	
	/*-------------------------------------------------------------------- DB 연결 ---------------------*/
	private Connection getConnection() throws Exception{
		Context initCtx = new InitialContext();
		Context envCtx = (Context)initCtx.lookup("java:comp/env");
		DataSource ds = (DataSource)envCtx.lookup("jdbc/orcl");
		return ds.getConnection();
	}
	
	/*----------------------------------------------------------------게시판의 글쓰기 처리-----------------*/
	public void insertArticle(Item item)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int item_num = item.getItem_num();
		int number = 0;
		String sql="";
		
		try{
			conn = getConnection();
			pstmt = conn.prepareStatement("select max(item_num) from ITEM");
				//ref 번호 부여하기 위해 수행
				rs = pstmt.executeQuery();
				if(rs.next()){
					number = rs.getInt(1)+1;
				}else{ // 최초 글 작성시
					number = 1;
				}
			// 쿼리를 작성
			
			int cnt=0;
			
			sql = "insert into ITEM(item_num,item_kind,item_name,item_date,readcount,item_content,item_img,item_passwd) "+
			                 "values (item_seq.nextval,?,?,sysdate,?,?,?,?)" ;
			pstmt= conn.prepareStatement(sql);
			pstmt.setString(++cnt,item.getItem_kind() );
			pstmt.setString(++cnt,item.getItem_name() );
			pstmt.setInt(++cnt, item.getReadcount());
			pstmt.setString(++cnt,item.getItem_content() );
			pstmt.setString(++cnt,item.getItem_img() );
			pstmt.setString(++cnt,item.getItem_passwd() );
			
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(pstmt != null)try{pstmt.close();}catch(SQLException e){}
			if(conn != null)try{conn.close();}catch(SQLException e){}
		}
	}
	
	/*--------------------------------------------------------------- 전체 글목록의 수를 얻어냄  ---------------------*/
	public int getArticleCount(String keyField, String keyWord)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs =null;
		String sql="";
		
		int x = 0;
		
		try{
			conn = getConnection();
			if(keyWord == null || "".equals(keyWord.trim())){ // 그냥 목록
				
				sql = "select count(*) from ITEM";
				pstmt = conn.prepareStatement(sql);
			
			}else{                                            // 검색했을때 목록 보여주기~~
			    //  keyField = 컬럼명 ,keyWord = 데이터

		    	keyField=new String(keyField.getBytes("iso-8859-1"), "utf-8"); // 한글 필터
		    	keyWord=new String(keyWord.getBytes("iso-8859-1"), "utf-8"); // 한글 필터
				System.out.println("keyField : " + keyField + "||" + "keyWord : "+ keyWord);
				sql = "select count(*) from ITEM where "+keyField+" like ?";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, "%"+keyWord+"%");
			}
			
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				x=rs.getInt(1);  //
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null)try{rs.close();}catch(SQLException e){}
			if(pstmt != null)try{pstmt.close();}catch(SQLException e){}
			if(conn != null)try{conn.close();}catch(SQLException e){}
		}
		return x;
	}
	
	/*-------------------------------------------------------------------- 게시판의 글목록 보기  ---------------------*/
	public List getArticles(int start, int end, String keyField, String keyWord)throws Exception{
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List articleList = null;
		String sql = "";
		
		try{
			conn = getConnection();
			
			if(keyWord == null || "".equals(keyWord.trim())){  /*------------ 목록---------*/
				sql = "select *"+
				 	  "from (select a.* , rownum rnum "+
				 	         "from (select * "+
				 	               "from ITEM "+
				                   "order by item_date desc)a) " +
				      "where rnum >= ? and rnum <=?";	
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, start);
				pstmt.setInt(2, end);
				
			}else{				/*--------------------------------------------검색 목록---------*/
				keyField=new String(keyField.getBytes("iso-8859-1"), "utf-8"); //한글 필터
		    	keyWord=new String(keyWord.getBytes("iso-8859-1"), "utf-8"); //한글 필터
				sql = "select *"+
			 	      "from (select a.* , rownum rnum "+
			 	             "from (select * "+
			 	                   "from ITEM "+
			 	                   "where "+keyField+" like ? "+
			                       "order by item_date desc)a) " +
			          "where rnum >= ? and rnum <=?";					
				pstmt = conn.prepareStatement(sql);				
				pstmt.setString(1, "%"+keyWord+"%");
				pstmt.setInt(2, start);
				pstmt.setInt(3, end);
			}
			 		
			
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				articleList = new ArrayList(end);
				do{
					Item article = new Item();
					article.setItem_num(rs.getInt("item_num"));
					article.setItem_kind(rs.getString("item_kind"));
					article.setItem_name(rs.getString("item_name"));
					article.setItem_img(rs.getString("item_img"));
					article.setItem_date(rs.getTimestamp("item_date"));
					article.setReadcount(rs.getInt("readcount"));
					article.setItem_content(rs.getString("item_content"));
					
					articleList.add(article);					
				}while(rs.next());				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null) try{rs.close();}catch(SQLException e){}
			if(pstmt != null) try{pstmt.close();}catch(SQLException e){}
			if(conn != null) try{conn.close();} catch(SQLException e){}
		}
		return articleList;
	}
	
	
	/*--------------------------------------------------------------------  조회수 & 상세보기 ---------------------*/
	public Item getArticle(int num) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Item article = null;
		
		try{
			//조회수
			conn = getConnection();
			pstmt = conn.prepareStatement("update ITEM set readcount = readcount + 1 where item_num=?");
			pstmt.setInt(1, num);			
			pstmt.executeUpdate();
				
			// 상세내용
			pstmt= conn.prepareStatement("select * from ITEM where item_num=?");
			pstmt.setInt(1, num);
			rs= pstmt.executeQuery();
			
			if (rs.next()) {
				article = new Item();
	            
				article.setItem_num(rs.getInt("item_num"));
				article.setItem_kind(rs.getString("item_kind"));
				article.setItem_name(rs.getString("item_name"));
				article.setItem_img(rs.getString("item_img"));
				article.setItem_date(rs.getTimestamp("item_date"));
				article.setReadcount(rs.getInt("readcount"));
				article.setItem_content(rs.getString("item_content")); 
				}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(rs != null) try{rs.close();}catch(SQLException e){}
			if(pstmt != null) try{pstmt.close();}catch(SQLException e){}
			if(conn != null) try{conn.close();} catch(SQLException e){}
		}
		return article;
	}
	
	
	/*-------------------------------------------------------- 상세보기 수정버튼 클릭시 내용 ---------------------*/
	
	public Item updateGetArticle(int item_num) throws Exception{
		Connection conn= null;
		PreparedStatement pstmt =null;
		ResultSet rs = null;
		Item article=null;
		
		try{
			conn = getConnection();
			pstmt= conn.prepareStatement("select * from item where item_num = ?");
		    
			pstmt.setInt(1, item_num);			
			rs = pstmt.executeQuery();
			
			 if (rs.next()) {
	                article = new Item();
	                article.setItem_num(rs.getInt("item_num"));
					article.setItem_kind(rs.getString("item_kind"));
					article.setItem_name(rs.getString("item_name"));
					article.setItem_img(rs.getString("item_img"));
					article.setItem_date(rs.getTimestamp("item_date"));
					article.setReadcount(rs.getInt("readcount"));
					article.setItem_content(rs.getString("item_content")); 
				}
			 
		}catch(Exception e){
			e.printStackTrace();
		}finally{
       		if (rs != null) try { rs.close(); } catch(SQLException ex) {}
			if(pstmt != null)try{conn.close();}catch(SQLException e){}
			if(conn != null )try{conn.close();}catch(SQLException e){}
		}
		return article;
	}
	
	
	/*------------------------------------------------------------------- 상세보기 수정---------------------*/
	public int updateArticle(Item article)throws Exception{
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs = null;
		
		String dbPasswd = "";	
		String sql="";
		int x=-1;
		
		try{
			conn =getConnection();
			pstmt=conn.prepareStatement("select item_passwd from ITEM where item_num=?");
			pstmt.setInt(1, article.getItem_num());
			rs = pstmt.executeQuery();
			int cnt=0;
			if(rs.next()){  //검증
				dbPasswd = rs.getString("item_passwd");
				if(dbPasswd.equals(article.getItem_passwd())){
					sql="update ITEM set item_kind=?,item_name=?, item_img=?";
					sql+=",item_content=? where item_num=?";
					pstmt= conn.prepareStatement(sql);
					
					 pstmt.setString(++cnt, article.getItem_kind());
		             pstmt.setString(++cnt, article.getItem_name());
		             pstmt.setString(++cnt, article.getItem_img());
		             pstmt.setString(++cnt, article.getItem_content());
					 pstmt.setInt(++cnt, article.getItem_num());
		             pstmt.executeUpdate();
		             
		             x =1;		                
				}else{
					x =0;					
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if (rs != null) try { rs.close(); } catch(SQLException ex) {}
			if(pstmt != null)try{conn.close();}catch(SQLException e){}
			if(conn != null )try{conn.close();}catch(SQLException e){}
		}
		return x;
	}
	///////////////////////////////////////////////////////////// 이미지
	public int makeFileNo()throws Exception {
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        ResultSet rs= null;
	        String sql ="";
	        int no = 0;
	        try {
				conn = getConnection();

	              sql ="select max(item_num) from item";
					pstmt = conn.prepareStatement(sql);
	                rs = pstmt.executeQuery();
	                if(rs.next()){
	                	no = rs.getInt(1);
	                }
	                no++;
	        } catch(Exception ex) {
	        	ex.printStackTrace();
	        } finally {
	            if (rs != null) try { rs.close(); } catch(SQLException ex) {}
	            if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
	            if (conn != null) try { conn.close(); } catch(SQLException ex) {}
	        }
			return no;
	    }
	
	/*------------------------------------------------------------------------------ 글 삭제 ---------------------*/
	public int deleteArticle(int item_num, String item_passwd) throws Exception{
		Connection conn =null;
		PreparedStatement pstmt =null;
		ResultSet rs = null;
		String dbPasswd="";
		int x = -1;
		
		try{
			conn =getConnection();
			pstmt=conn.prepareStatement("select item_passwd from ITEM where item_num=?");
			pstmt.setInt(1, item_num);
			rs = pstmt.executeQuery();
			
			if(rs.next()){  //검증
				dbPasswd = rs.getString("item_passwd");
				if(dbPasswd.equals(item_passwd)){
					
					pstmt= conn.prepareStatement( "delete from item where item_num=?");
				    pstmt.setInt(1, item_num);
		            pstmt.executeUpdate();		             
		            x =1;	//글삭제 성공	                
				}else{
					x =0;	 //비밀번호 틀림				
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if (rs != null) try { rs.close(); } catch(SQLException ex) {}
			if(pstmt != null)try{conn.close();}catch(SQLException e){}
			if(conn != null )try{conn.close();}catch(SQLException e){}
		}
		
		return x;
	}
}